<!DOCTYPE html><html lang="zh-CN" data-theme="light"><head><meta charset="UTF-8"><meta http-equiv="X-UA-Compatible" content="IE=edge"><meta name="viewport" content="width=device-width,initial-scale=1"><title>SQLServer📦数据查询 | 小李博客</title><meta name="keywords" content="SQLServer"><meta name="author" content="小李博客"><meta name="copyright" content="小李博客"><meta name="format-detection" content="telephone=no"><meta name="theme-color" content="#ffffff"><meta name="description" content="Select查询语句1234567select [all|distinct] [top n] 表达式列表 --- 投影查询[into 新表名] 	--- 保存查询from 基本表列表| 视图名列表 --- 连接查询[where 查询条件] --- 选择查询[group by 分组列名表] --- 分组统计查询[having 逻辑表达式] 	--- 限定查询[order by 排序列名表[ASC|D">
<meta property="og:type" content="article">
<meta property="og:title" content="SQLServer📦数据查询">
<meta property="og:url" content="http://xiaoliblog.cn/page/sqlserver06.html">
<meta property="og:site_name" content="小李博客">
<meta property="og:description" content="Select查询语句1234567select [all|distinct] [top n] 表达式列表 --- 投影查询[into 新表名] 	--- 保存查询from 基本表列表| 视图名列表 --- 连接查询[where 查询条件] --- 选择查询[group by 分组列名表] --- 分组统计查询[having 逻辑表达式] 	--- 限定查询[order by 排序列名表[ASC|D">
<meta property="og:locale" content="zh_CN">
<meta property="og:image" content="https://cdn.jsdelivr.net/gh/lzyblog/image@main/2021/01/01/09bc0b6b22736e2ddda50dbb14f3e553.png">
<meta property="article:published_time" content="2021-01-16T10:34:28.351Z">
<meta property="article:modified_time" content="2021-02-06T12:39:11.223Z">
<meta property="article:author" content="小李博客">
<meta property="article:tag" content="SQLServer">
<meta name="twitter:card" content="summary">
<meta name="twitter:image" content="https://cdn.jsdelivr.net/gh/lzyblog/image@main/2021/01/01/09bc0b6b22736e2ddda50dbb14f3e553.png"><link rel="shortcut icon" href="https://cdn.jsdelivr.net/gh/xiaoliblog/image@6b5e7ef72be1c8973d94e5a9c49accbf775ad820/2021/02/01/c485da031fe0e464d04eaba8a66c4a8f.png"><link rel="canonical" href="http://xiaoliblog.cn/page/sqlserver06"><link rel="preconnect" href="//cdn.jsdelivr.net"/><link rel="preconnect" href="//busuanzi.ibruce.info"/><link rel="stylesheet" href="/css/index.css"><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@fortawesome/fontawesome-free/css/all.min.css" media="print" onload="this.media='all'"><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/node-snackbar/dist/snackbar.min.css" media="print" onload="this.media='all'"><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/instantsearch.js@2.10.5/dist/instantsearch.min.css" media="print" onload="this.media='all'"><script src="https://cdn.jsdelivr.net/npm/instantsearch.js@2.10.5/dist/instantsearch.min.js" defer></script><script>const GLOBAL_CONFIG = { 
  root: '/',
  algolia: {"appId":"RTG4CPNBLJ","apiKey":"f1745bdad68ceec57653b78244fe332c","indexName":"MyBlogIndex","hits":{"per_page":6},"languages":{"input_placeholder":"搜索文章","hits_empty":"找不到您查询的内容：${query}","hits_stats":"找到 ${hits} 条结果，用时 ${time} 毫秒"}},
  localSearch: undefined,
  translate: {"defaultEncoding":2,"translateDelay":0,"msgToTraditionalChinese":"繁","msgToSimplifiedChinese":"簡"},
  noticeOutdate: undefined,
  highlight: {"plugin":"highlighjs","highlightCopy":true,"highlightLang":true,"highlightHeightLimit":200},
  copy: {
    success: '复制成功',
    error: '复制错误',
    noSupport: '浏览器不支持'
  },
  relativeDate: {
    homepage: false,
    post: false
  },
  runtime: '天',
  date_suffix: {
    just: '刚刚',
    min: '分钟前',
    hour: '小时前',
    day: '天前',
    month: '个月前'
  },
  copyright: {"limitCount":100,"languages":{"author":"作者: 小李博客","link":"链接: ","source":"来源: 小李博客","info":"著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。"}},
  lightbox: 'mediumZoom',
  Snackbar: {"chs_to_cht":"你已切换为繁体","cht_to_chs":"你已切换为简体","day_to_night":"你已切换为深色模式","night_to_day":"你已切换为浅色模式","bgLight":"#49b1f5","bgDark":"#121212","position":"top-center"},
  source: {
    jQuery: 'https://cdn.jsdelivr.net/npm/jquery@latest/dist/jquery.min.js',
    justifiedGallery: {
      js: 'https://cdn.jsdelivr.net/npm/justifiedGallery/dist/js/jquery.justifiedGallery.min.js',
      css: 'https://cdn.jsdelivr.net/npm/justifiedGallery/dist/css/justifiedGallery.min.css'
    },
    fancybox: {
      js: 'https://cdn.jsdelivr.net/npm/@fancyapps/fancybox@latest/dist/jquery.fancybox.min.js',
      css: 'https://cdn.jsdelivr.net/npm/@fancyapps/fancybox@latest/dist/jquery.fancybox.min.css'
    }
  },
  isPhotoFigcaption: false,
  islazyload: false,
  isanchor: true
}</script><script id="config-diff">var GLOBAL_CONFIG_SITE = { 
  isPost: true,
  isHome: false,
  isHighlightShrink: false,
  isToc: true,
  postUpdate: '2021-02-06 20:39:11'
}</script><noscript><style type="text/css">
  #nav {
    opacity: 1
  }
  .justified-gallery img {
    opacity: 1
  }

  #recent-posts time,
  #post-meta time {
    display: inline !important
  }
</style></noscript><script>(win=>{
    win.saveToLocal = {
      set: function setWithExpiry(key, value, ttl) {
        if (ttl === 0) return
        const now = new Date()
        const expiryDay = ttl * 86400000
        const item = {
          value: value,
          expiry: now.getTime() + expiryDay,
        }
        localStorage.setItem(key, JSON.stringify(item))
      },

      get: function getWithExpiry(key) {
        const itemStr = localStorage.getItem(key)

        if (!itemStr) {
          return undefined
        }
        const item = JSON.parse(itemStr)
        const now = new Date()

        if (now.getTime() > item.expiry) {
          localStorage.removeItem(key)
          return undefined
        }
        return item.value
      }
    }
  
    win.getScript = url => new Promise((resolve, reject) => {
      const script = document.createElement('script')
      script.src = url
      script.async = true
      script.onerror = reject
      script.onload = script.onreadystatechange = function() {
        const loadState = this.readyState
        if (loadState && loadState !== 'loaded' && loadState !== 'complete') return
        script.onload = script.onreadystatechange = null
        resolve()
      }
      document.head.appendChild(script)
    })
  
      win.activateDarkMode = function () {
        document.documentElement.setAttribute('data-theme', 'dark')
        if (document.querySelector('meta[name="theme-color"]') !== null) {
          document.querySelector('meta[name="theme-color"]').setAttribute('content', '#0d0d0d')
        }
      }
      win.activateLightMode = function () {
        document.documentElement.setAttribute('data-theme', 'light')
        if (document.querySelector('meta[name="theme-color"]') !== null) {
          document.querySelector('meta[name="theme-color"]').setAttribute('content', '#ffffff')
        }
      }
      const t = saveToLocal.get('theme')
    
          if (t === 'dark') activateDarkMode()
          else if (t === 'light') activateLightMode()
        
      const asideStatus = saveToLocal.get('aside-status')
      if (asideStatus !== undefined) {
        if (asideStatus === 'hide') {
          document.documentElement.classList.add('hide-aside')
        } else {
          document.documentElement.classList.remove('hide-aside')
        }
      }
    
    const fontSizeVal = saveToLocal.get('global-font-size')
    if (fontSizeVal !== undefined) {
      document.documentElement.style.setProperty('--global-font-size', fontSizeVal + 'px')
    }
    })(window)</script><link rel="stylesheet" href="/css/MyStyle/MyStyle.css" media="defer" onload="this.media='all'"/><link rel="stylesheet" href="/css/MyStyle/tagStyle.css" media="defer" onload="this.media='all'"/><link rel="stylesheet" href="https://cdn.jsdelivr.net/gh/zykjofficial/zykjresource@master/css/font-awesome-animation.min.css" media="defer" onload="this.media='all'"/><link rel="stylesheet" href="https://cdn.jsdelivr.net/gh/sviptzk/StaticFile_HEXO@latest/butterfly/css/font-awesome-animation.min.css" media="defer" onload="this.media='all'"><link rel="stylesheet" href="https://cdn.jsdelivr.net/gh/sviptzk/StaticFile_HEXO@latest/butterfly/css/plugins.min.css" media="defer" onload="this.media='all'"><meta name="generator" content="Hexo 5.2.0"><link rel="alternate" href="/atom.xml" title="小李博客" type="application/atom+xml">
</head><body><div id="web_bg"></div><div id="sidebar"><div id="menu-mask"></div><div id="sidebar-menus"><div class="author-avatar"><img class="avatar-img" src="https://cdn.jsdelivr.net/gh/xiaoliblog/image@6b5e7ef72be1c8973d94e5a9c49accbf775ad820/2021/02/01/c485da031fe0e464d04eaba8a66c4a8f.png" onerror="onerror=null;src='/img/friend_404.gif'" alt="avatar"/></div><div class="site-data"><div class="data-item is-center"><div class="data-item-link"><a href="/archives/"><div class="headline">文章</div><div class="length-num">210</div></a></div></div><div class="data-item is-center"><div class="data-item-link"><a href="/tags/"><div class="headline">标签</div><div class="length-num">38</div></a></div></div><div class="data-item is-center"><div class="data-item-link"><a href="/categories/"><div class="headline">分类</div><div class="length-num">56</div></a></div></div></div><hr/><div class="menus_items"><div class="menus_item"><a class="site-page" href="/"><i class="fa-fw fas fa-home"></i><span> 主页</span></a></div><div class="menus_item"><a class="site-page" href="/link/"><i class="fa-fw fas fa-link"></i><span> 友链</span></a></div><div class="menus_item"><a class="site-page" href="/box/"><i class="fa-fw fa fa-briefcase"></i><span> 工具箱</span></a></div><div class="menus_item"><a class="site-page" href="javascript:void(0);"><i class="fa-fw fas fa-book"></i><span> 找文章</span><i class="fas fa-chevron-down expand"></i></a><ul class="menus_item_child"><li><a class="site-page child" href="/archives/"><i class="fa-fw fas fa-archive"></i><span> 归档</span></a></li><li><a class="site-page child" href="/tags/"><i class="fa-fw fas fa-tags"></i><span> 标签</span></a></li><li><a class="site-page child" href="/categories/"><i class="fa-fw fa fa-folder-open"></i><span> 分类</span></a></li></ul></div><div class="menus_item"><a class="site-page" href="/about/"><i class="fa-fw fa fa-address-card"></i><span> 关于</span></a></div><div class="menus_item"><a class="site-page" href="/messageboard/"><i class="fa-fw fa fa-paper-plane"></i><span> 留言</span></a></div></div></div></div><div class="post" id="body-wrap"><header class="not-top-img" id="page-header"><nav id="nav"><span id="blog_name"><a id="site-name" href="/">小李博客</a></span><div id="menus"><div id="search-button"><a class="site-page social-icon search"><i class="fas fa-search fa-fw"></i><span> 搜索</span></a></div><div class="menus_items"><div class="menus_item"><a class="site-page" href="/"><i class="fa-fw fas fa-home"></i><span> 主页</span></a></div><div class="menus_item"><a class="site-page" href="/link/"><i class="fa-fw fas fa-link"></i><span> 友链</span></a></div><div class="menus_item"><a class="site-page" href="/box/"><i class="fa-fw fa fa-briefcase"></i><span> 工具箱</span></a></div><div class="menus_item"><a class="site-page" href="javascript:void(0);"><i class="fa-fw fas fa-book"></i><span> 找文章</span><i class="fas fa-chevron-down expand"></i></a><ul class="menus_item_child"><li><a class="site-page child" href="/archives/"><i class="fa-fw fas fa-archive"></i><span> 归档</span></a></li><li><a class="site-page child" href="/tags/"><i class="fa-fw fas fa-tags"></i><span> 标签</span></a></li><li><a class="site-page child" href="/categories/"><i class="fa-fw fa fa-folder-open"></i><span> 分类</span></a></li></ul></div><div class="menus_item"><a class="site-page" href="/about/"><i class="fa-fw fa fa-address-card"></i><span> 关于</span></a></div><div class="menus_item"><a class="site-page" href="/messageboard/"><i class="fa-fw fa fa-paper-plane"></i><span> 留言</span></a></div></div><div id="toggle-menu"><a class="site-page"><i class="fas fa-bars fa-fw"></i></a></div></div></nav></header><main class="layout" id="content-inner"><div id="post"><div id="post-info"><h1 class="post-title">SQLServer📦数据查询</h1><div id="post-meta"><div class="meta-firstline"><span class="post-meta-date"><i class="far fa-calendar-alt fa-fw post-meta-icon"></i><span class="post-meta-label">发表于</span><time class="post-meta-date-created" datetime="2021-01-16T10:34:28.351Z" title="发表于 2021-01-16 18:34:28">2021-01-16</time><span class="post-meta-separator">|</span><i class="fas fa-history fa-fw post-meta-icon"></i><span class="post-meta-label">更新于</span><time class="post-meta-date-updated" datetime="2021-02-06T12:39:11.223Z" title="更新于 2021-02-06 20:39:11">2021-02-06</time></span><span class="post-meta-categories"><span class="post-meta-separator">|</span><i class="fas fa-inbox fa-fw post-meta-icon"></i><a class="post-meta-categories" href="/categories/%E6%95%B0%E6%8D%AE%E5%BA%93/">数据库</a><i class="fas fa-angle-right post-meta-separator"></i><i class="fas fa-inbox fa-fw post-meta-icon"></i><a class="post-meta-categories" href="/categories/%E6%95%B0%E6%8D%AE%E5%BA%93/SQLServer/">SQLServer</a></span></div><div class="meta-secondline"><span class="post-meta-separator">|</span><span class="post-meta-wordcount"><i class="far fa-file-word fa-fw post-meta-icon"></i><span class="post-meta-label">字数总计:</span><span class="word-count">5k</span><span class="post-meta-separator">|</span><i class="far fa-clock fa-fw post-meta-icon"></i><span class="post-meta-label">阅读时长:</span><span>18分钟</span></span><span class="post-meta-separator">|</span><span class="post-meta-pv-cv" id="" data-flag-title="SQLServer📦数据查询"><i class="far fa-eye fa-fw post-meta-icon"></i><span class="post-meta-label">阅读量:</span><span id="busuanzi_value_page_pv"></span></span></div></div></div><article class="post-content" id="article-container"><h1 id="Select查询语句"><a href="#Select查询语句" class="headerlink" title="Select查询语句"></a>Select查询语句</h1><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> [<span class="keyword">all</span>|<span class="keyword">distinct</span>] [top n] 表达式列表 <span class="comment">--- 投影查询</span></span><br><span class="line">[<span class="keyword">into</span> 新表名] 	<span class="comment">--- 保存查询</span></span><br><span class="line"><span class="keyword">from</span> 基本表列表| 视图名列表 <span class="comment">--- 连接查询</span></span><br><span class="line">[<span class="keyword">where</span> 查询条件] <span class="comment">--- 选择查询</span></span><br><span class="line">[<span class="keyword">group</span> <span class="keyword">by</span> 分组列名表] <span class="comment">--- 分组统计查询</span></span><br><span class="line">[<span class="keyword">having</span> 逻辑表达式] 	<span class="comment">--- 限定查询</span></span><br><span class="line">[<span class="keyword">order</span> <span class="keyword">by</span> 排序列名表[<span class="keyword">ASC</span>|<span class="keyword">DESC</span>]] <span class="comment">---排序查询</span></span><br></pre></td></tr></table></figure>
<h2 id="Select子句—投影查询"><a href="#Select子句—投影查询" class="headerlink" title="Select子句—投影查询"></a>Select子句—投影查询</h2><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> [ <span class="keyword">ALL</span> | <span class="keyword">DISTINCT</span> ] [TOP n]  表达式<span class="number">1</span>, … , 表达式n</span><br></pre></td></tr></table></figure>
<p>用于描述查询结果集的列 ， 它是一个用逗号分隔的表达式列表 。这里的表达式由<code>列名 、 常量 、 函数和运算符</code>构成 。 此外 ， 还有一些参数可以根据需要进行选择 </p>
<ul>
<li><strong>列名表：投影某些列进行查询</strong></li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/* 从教务管理数据库“EDUC ”的学生表“Student”中查询出男生的学号、姓名、性别和专业的数据信息。*/</span></span><br><span class="line"><span class="keyword">USE</span> EDUC <span class="comment">-- 设置当前数据库为EDUC</span></span><br><span class="line"><span class="keyword">GO</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="keyword">SID</span>,Sname,Sex,Specialty <span class="comment">-- 投影列( 列选)</span></span><br><span class="line"><span class="keyword">FROM</span> Student <span class="comment">-- 查询的表“Student” ”</span></span><br><span class="line"><span class="keyword">WHERE</span> Sex=<span class="string">&#x27;男&#x27;</span> <span class="comment">-- 选择性别为“男”的数据行(</span></span><br></pre></td></tr></table></figure>

<ul>
<li><strong>TOP关键字限制：返回行数</strong></li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">TOP n [PERCENT]</span><br></pre></td></tr></table></figure>
<p>如果未指定关键字PERCENT， 则返回查询结果集的前n 行数据 。如果指定了关键字PERCENT，n 就是查询返回结果集行的百分比</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*从教务管理数据库“EDUC”的课程表“Course” 中查询出前3行数据*/</span></span><br><span class="line"><span class="keyword">SELECT</span> TOP <span class="number">3</span> CID, Cname, Credit <span class="comment">-- 返回前 3 行数据</span></span><br><span class="line"><span class="keyword">FROM</span> Course</span><br><span class="line"></span><br><span class="line"><span class="keyword">SELECT</span> TOP <span class="number">20</span> <span class="keyword">Percent</span> CID, Cname, Credit <span class="comment">-- 返回前 20% 行数据</span></span><br><span class="line"><span class="keyword">FROM</span> Course</span><br></pre></td></tr></table></figure>

<ul>
<li><strong>是否消除重复数据行</strong></li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">All | DISTINCT</span><br></pre></td></tr></table></figure>
<p>指定<code>All(缺省)</code>关键字将保留查询结果集中的全部数据行 。当对表进行投影操作之后 ， 在查询结果集中可能会出现重复的数据行 ，使用<code>DISTINCT关键字可消除查询结果集中的重复数据行</code></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*从教务管理数据库“EDUC”的学生表“Student”中查询出学校各专业的名称*/</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="keyword">DISTINCT</span> Specialty <span class="comment">-- 使用DISTINCT 消除重复数据行</span></span><br><span class="line"><span class="keyword">FROM</span> Student</span><br></pre></td></tr></table></figure>

<ul>
<li><strong>通配符*:投影所有列</strong></li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*从教务管理数据库“EDUC”的学生表“Student”中查询出前3行数据*/</span></span><br><span class="line"><span class="keyword">SELECT</span> TOP <span class="number">3</span> *  <span class="comment">--&quot;*&quot;代表投影所有列</span></span><br><span class="line"><span class="keyword">FROM</span> Student</span><br></pre></td></tr></table></figure>

<ul>
<li><strong>使用表达式计算列值</strong><br>在SELECT子句中可以使用加 (+) 、 减(−)  、 乘(*) 、除(/) 、 取模(%)和字符连接(+)等运算符及各种函数进行运算 ， 通过对表达式的计算来获取查询结果的列值 。</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*从教务管理数据库“EDUC”的课程“Course”表里查询出学分对应的课程学时（假设每18学时计1学分），显示前5行*/</span></span><br><span class="line"><span class="comment">--- 表达式含运算符</span></span><br><span class="line"><span class="keyword">SELECT</span> TOP <span class="number">5</span> CID, Cname, Credit, Credit*<span class="number">18</span></span><br><span class="line"><span class="keyword">FROM</span> Course</span><br><span class="line"></span><br><span class="line"><span class="comment">--- 表达式带函数</span></span><br><span class="line"><span class="keyword">SELECT</span> TOP <span class="number">5</span> CID, Cname, Credit,</span><br><span class="line"><span class="keyword">STR</span>(Credit * <span class="number">18</span>, <span class="number">3</span>, <span class="number">0</span>) + <span class="string">&#x27;学时&#x27;</span></span><br><span class="line"><span class="keyword">FROM</span> Course</span><br><span class="line"></span><br><span class="line"><span class="comment">--- 表达式为常量</span></span><br><span class="line"><span class="keyword">SELECT</span> TOP <span class="number">5</span> CID, Cname, Credit, <span class="string">&#x27;学分&#x27;</span> ,</span><br><span class="line"><span class="keyword">STR</span>( Credit * <span class="number">18</span>, <span class="number">3</span>, <span class="number">0</span>), <span class="string">&#x27;学时&#x27;</span></span><br><span class="line"><span class="keyword">FROM</span> Course</span><br></pre></td></tr></table></figure>

<ul>
<li><strong>自定义列标题</strong></li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">1：列名 AS &#x27;指定的列标题&#x27;  (AS可以省略)</span><br><span class="line">2：&#x27;指定的列标题&#x27; = 列名表达式</span><br></pre></td></tr></table></figure>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> TOP <span class="number">5</span> CID <span class="keyword">AS</span> <span class="string">&#x27;课程号&#x27;</span>, Cname <span class="keyword">AS</span> <span class="string">&#x27;课程名&#x27;</span>,</span><br><span class="line">Credit 学分, 学时 = <span class="keyword">STR</span>( Credit * <span class="number">18</span>, <span class="number">3</span>, <span class="number">0</span>)</span><br><span class="line"><span class="keyword">FROM</span> Course</span><br></pre></td></tr></table></figure>

<ul>
<li><strong>聚合函数</strong></li>
</ul>
<figure class="highlight"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">聚合函数名( [ALL | DISTINCT] 列名表达式 | * )</span><br></pre></td></tr></table></figure>
<p><code>ALL</code>关键字表示函数对指定列的所有值进行统计和计算， <code>DISTINCT</code>关键字说明函数仅对指定列的唯一值 （ 不计重复值 ） 进行统计和计算， ALL为默认设置</p>
<ul>
<li>COUNT：统计 列 中选取的项目个数或 或  查询输出的行数 。</li>
<li>SUM：计算指定的数值型 列 名表达式的总和 。</li>
<li>AVG：计算指定的数值型 列 名表达式的平均值 。</li>
<li>MAX：求出指定的数值 、 字符或日期型 列 名表达式的最大值 。</li>
<li>MIN：求出指定的数值 、 字符或日期型 列 名表达式的最小值</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*从学生表“ 从学生表“Student”中统计出学生的总人数、录取分数“AScores”的最高分、最低分、总分和平均分*/</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="keyword">COUNT</span>(*) <span class="keyword">AS</span> 总人数,  <span class="comment">-- 统计总数</span></span><br><span class="line">	<span class="keyword">MAX</span>(AScores) <span class="keyword">AS</span> 最高分,  <span class="comment">-- 求最大</span></span><br><span class="line">	<span class="keyword">MIN</span>(AScores) <span class="keyword">AS</span> 最低分, <span class="comment">-- 求最小</span></span><br><span class="line">	<span class="keyword">SUM</span>(AScores) <span class="keyword">AS</span> 总分,  <span class="comment">-- 求和</span></span><br><span class="line">	<span class="keyword">AVG</span>(AScores) <span class="keyword">AS</span> 平均分  <span class="comment">-- 求平均分</span></span><br><span class="line"><span class="keyword">FROM</span> Student</span><br><span class="line"></span><br><span class="line"><span class="comment">/*从学生表“Student”中统计出男生的人数*/</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="keyword">COUNT</span>(*) <span class="keyword">AS</span> 人数  <span class="comment">-- 统计表中满足条件的行数</span></span><br><span class="line"><span class="keyword">FROM</span> Student</span><br><span class="line"><span class="keyword">WHERE</span> Sex=<span class="string">&#x27;男&#x27;</span> </span><br><span class="line"></span><br><span class="line"><span class="comment">/* 从学生表“Student”中统计出专业个数。注意，DISTINCT关键字的作用是消除重复行，即每个专业只计一次*/</span></span><br><span class="line"><span class="comment">-- 统计表“Student ”中名称不重复的专业个数</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="keyword">COUNT</span>(<span class="keyword">DISTINCT</span> Specialty) <span class="keyword">AS</span> 专业个数 </span><br><span class="line"><span class="keyword">FROM</span> Student</span><br></pre></td></tr></table></figure>
<h2 id="From子句—连接查询"><a href="#From子句—连接查询" class="headerlink" title="From子句—连接查询"></a>From子句—连接查询</h2><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">FROM 基本表1, 基本表2, … , 基本表n</span><br></pre></td></tr></table></figure>
<p><code>指定要查询的基本表</code> 。如果指定了一个以上的基本表 ， 则计算它们之间的<code>笛卡儿积</code>， 与WHERE子句等值条件配合实现连接查询</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*从教务管理数据库“EDUC”中查询出学生的学号、姓名、所选课程名和成绩信息*/</span></span><br><span class="line"><span class="keyword">USE</span> EDUC</span><br><span class="line"><span class="keyword">GO</span></span><br><span class="line"><span class="keyword">SELECT</span> Student.SID,Sname,Cname,Scores <span class="comment">-- 投影各表的列</span></span><br><span class="line"><span class="keyword">FROM</span> Student, SC, Course <span class="comment">-- 连接3 张表, 对3 张表求笛卡尔积</span></span><br><span class="line"><span class="keyword">WHERE</span> Student.SID=SC.SID <span class="keyword">AND</span> SC.CID=Course.CID <span class="comment">--等值连接</span></span><br></pre></td></tr></table></figure>

<ul>
<li>为基本表指定临时别名 —— 目的：简化表名</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">基本表名 [AS] 别名 别名</span><br></pre></td></tr></table></figure>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*从教务管理数据库“EDUC”中查询出学生的学号、姓名、所选课程名和成绩信息*/</span></span><br><span class="line"><span class="keyword">USE</span> EDUC</span><br><span class="line"><span class="keyword">GO</span></span><br><span class="line"><span class="keyword">SELECT</span> x.SID, x.Sname, z.Cname, y.Scores<span class="comment">-- 投影各表的列</span></span><br><span class="line"><span class="keyword">FROM</span> Student x, SC y, Course z</span><br><span class="line"><span class="comment">-- 为三个表指定别名</span></span><br><span class="line"><span class="keyword">WHERE</span> x.SID=y.SID <span class="keyword">AND</span> y.CID=z.CID <span class="comment">-- 等值条件连接</span></span><br></pre></td></tr></table></figure>
<h2 id="Where子句—选择查询"><a href="#Where子句—选择查询" class="headerlink" title="Where子句—选择查询"></a>Where子句—选择查询</h2><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">WHERE 逻辑表达式</span><br></pre></td></tr></table></figure>

<ul>
<li>WHERE 子句用于 <code>行选择</code> 操作，定义了表中的行要满足条件（逻辑表达式为真)才能参与SELECT 查询语句的操作</li>
<li>逻辑表达式 ：由 常量、列名（变量）、函数、运算符（ 算术、关系、逻辑等）等组成，值为true( 非零/ 真) 或false( 零/ 假）</li>
</ul>
<table>
<thead>
<tr>
<th align="center">运算符</th>
<th align="center">形式</th>
</tr>
</thead>
<tbody><tr>
<td align="center">比较运算符</td>
<td align="center">=、 &lt;&gt; 、 &gt; 、&lt; 、&gt;= 、&lt;= 、!&gt; 、!&lt;</td>
</tr>
<tr>
<td align="center">逻辑运算符</td>
<td align="center">and、or、not</td>
</tr>
<tr>
<td align="center">范围运算符</td>
<td align="center">between、not between</td>
</tr>
<tr>
<td align="center">字符串匹配</td>
<td align="center">like、not like</td>
</tr>
<tr>
<td align="center">列表运算符</td>
<td align="center">in、not in</td>
</tr>
<tr>
<td align="center">空值判断符</td>
<td align="center">is null、is not null</td>
</tr>
</tbody></table>
<ul>
<li><strong>关系运算符</strong></li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*从学生表“Student”中查询出学生“赵成刚”的信息*/</span></span><br><span class="line"><span class="keyword">SELECT</span> *</span><br><span class="line"><span class="keyword">FROM</span> Student</span><br><span class="line"><span class="keyword">WHERE</span> Sname = <span class="string">&#x27;赵成刚&#x27;</span>   <span class="comment">--关系运算</span></span><br></pre></td></tr></table></figure>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*从学生表“Student”中查出入学分数高于 300分的学生的学号、姓名 、分数*/</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="keyword">SID</span>, Sname, AScores</span><br><span class="line"><span class="keyword">FROM</span> Student</span><br><span class="line"><span class="keyword">WHERE</span> AScores &gt; <span class="number">300</span>   <span class="comment">--关系运算</span></span><br></pre></td></tr></table></figure>

<ul>
<li><strong>逻辑运算符</strong><br>逻辑运算符：AND （与）、OR（或） 、 NOT（非）</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*从学生表“Student”中查出入学分数高于 300分的女生的学号、姓名 、分数*/</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="keyword">SID</span>, Sname, AScores</span><br><span class="line"><span class="keyword">FROM</span> Student</span><br><span class="line"><span class="keyword">WHERE</span> AScores &gt; <span class="number">300</span> <span class="keyword">AND</span> Sex=<span class="string">&#x27;女&#x27;</span>  <span class="comment">--逻辑运算</span></span><br></pre></td></tr></table></figure>

<ul>
<li><strong>范围运算符</strong></li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">列名 [NOT] BETWEEN 开始值 AND 结束值</span><br><span class="line"><span class="comment">-- 指定列名是否在开始值和结束值之间</span></span><br></pre></td></tr></table></figure>

<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*从学生表“Student”中查询出入学分数在350分到360分之间的学生信息*/</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> Student</span><br><span class="line"><span class="keyword">WHERE</span> AScores <span class="keyword">BETWEEN</span> <span class="number">350</span> <span class="keyword">AND</span> <span class="number">360</span> <span class="comment">-- 范围运算</span></span><br><span class="line"><span class="comment">--- 或： WHERE AScores&gt;=350 AND AScores &lt;=360</span></span><br></pre></td></tr></table></figure>

<ul>
<li><strong>模式匹配运算符</strong></li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">列名 [NOT] LIKE 字符串（含通配符）</span><br></pre></td></tr></table></figure>
<p><code>通配符“\_” </code>代表任意一个字符；<br><code>通配符“%” </code>代表任意多个字符。<br>模式匹配运算符LIKE可以<code>实现对表的模糊查询 </code></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*从学生表“Student”中查询出“李”姓的学生信息*/</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> Student</span><br><span class="line"><span class="keyword">WHERE</span> Sname <span class="keyword">LIKE</span> <span class="string">&#x27;李%&#x27;</span> <span class="comment">--- 模式匹配模糊查询</span></span><br></pre></td></tr></table></figure>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*从课程表“Course”中查询出有关&quot;程序设计&quot;方面的课程*/</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> Course</span><br><span class="line"><span class="keyword">WHERE</span> Cname <span class="keyword">LIKE</span> <span class="string">&#x27;%程序设计%&#x27;</span> <span class="comment">-- 模式匹配模糊查询</span></span><br></pre></td></tr></table></figure>

<ul>
<li><strong>列表运算符</strong></li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">列名 [NOT] IN (列表|子查) </span><br></pre></td></tr></table></figure>
<p>列名的值（不）是列表或子查询结果中的任何一个值时，逻辑表达式的值为（假）真</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*学生表“Student”中查询学号为2011216007和2012216089的学生信息*/</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> Student</span><br><span class="line"><span class="keyword">WHERE</span> <span class="keyword">SID</span> <span class="keyword">IN</span>(<span class="string">&#x27;2011216007&#x27;</span>,<span class="string">&#x27;2012216089&#x27;</span>)</span><br><span class="line">	<span class="comment">-- 列名判断运算</span></span><br><span class="line">	<span class="comment">-- 等价于：SID= &#x27;2011216007&#x27;OR SID=&#x27;2012216089&#x27;</span></span><br></pre></td></tr></table></figure>

<ul>
<li><strong>空值运算符</strong></li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">列名 IS [NOT] NULL</span><br></pre></td></tr></table></figure>

<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*从教务管理数据库“EDUC”的选课表“SC”中查询出考试成绩没有登记的记录*/</span></span><br><span class="line"><span class="keyword">SELECT</span> *</span><br><span class="line"><span class="keyword">FROM</span> SC</span><br><span class="line"><span class="keyword">WHERE</span> Scores <span class="keyword">IS</span> <span class="literal">NULL</span> <span class="comment">-- 空值判断运算</span></span><br></pre></td></tr></table></figure>
<h2 id="Group-by子句—分组统计查询"><a href="#Group-by子句—分组统计查询" class="headerlink" title="Group by子句—分组统计查询"></a>Group by子句—分组统计查询</h2><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">GROUP BY 列名表</span><br></pre></td></tr></table></figure>

<ul>
<li>与<code>列名</code>或 <code>聚合函数</code> 配合实现分组统计</li>
<li>实际应用中往往需要根据某列的值进行分组统计与汇总</li>
<li>如 ， 需要从学生表中统计出 <code>各专业 </code>的学生总数 ， 统计时用 “<code>GROUP BY专业名称 </code> ” 进行分组 ， 然后计算聚合函数的值</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*从学生表“Student”中查询出各专业学生入学的平均分*/</span></span><br><span class="line"><span class="keyword">SELECT</span> SpecialtyAS <span class="string">&#x27;专业&#x27;</span>,<span class="keyword">AVG</span>(AScores) <span class="keyword">AS</span> <span class="string">&#x27;平均分&#x27;</span>  <span class="comment">-- 求各专业入学平均分</span></span><br><span class="line"><span class="keyword">FROM</span> Student</span><br><span class="line"><span class="keyword">GROUP</span> <span class="keyword">BY</span> Specialty   <span class="comment">--根据专业名称分组</span></span><br></pre></td></tr></table></figure>
<h2 id="Having子句—限定查询"><a href="#Having子句—限定查询" class="headerlink" title="Having子句—限定查询"></a>Having子句—限定查询</h2><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">HAVING 逻辑表达式</span><br></pre></td></tr></table></figure>

<ul>
<li>与GROUP BY配合使用，筛选统计的结果</li>
<li>HAVING子句的逻辑表达式通常包含聚合函数</li>
<li>如果不使用GROUP BY  子句，HAVING  的行为与 WHERE 子句一样</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*从学生表“Student”中查询出各专业学生入学平均分高于340分的专业和入学平均分*/</span></span><br><span class="line"><span class="keyword">SELECT</span> Specialty <span class="keyword">AS</span>  专业, <span class="keyword">AVG</span>(AScores) <span class="keyword">AS</span> <span class="string">&#x27;平均分&#x27;</span>  <span class="comment">-- 求各专业入学平均分</span></span><br><span class="line"><span class="keyword">FROM</span> Student</span><br><span class="line"><span class="keyword">GROUP</span> <span class="keyword">BY</span> Specialty <span class="comment">-- 根据专业名称分组</span></span><br><span class="line"><span class="keyword">HAVING</span> <span class="keyword">AVG</span>(AScores) &gt; <span class="number">340</span> <span class="comment">--对学生总成绩进行筛选</span></span><br></pre></td></tr></table></figure>

<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*从学生表“Student”中查询出各专业的学生总数&gt;4的专业名称和人数 */</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="string">&#x27;专业&#x27;</span> = Specialty, <span class="string">&#x27;人数&#x27;</span> = <span class="keyword">COUNT</span>(*)  <span class="comment">-- 统计各专业学生人数</span></span><br><span class="line"><span class="keyword">FROM</span> Student</span><br><span class="line"><span class="keyword">GROUP</span> <span class="keyword">BY</span> Specialty <span class="comment">-- 根据专业名称分组</span></span><br><span class="line"><span class="keyword">HAVING</span> <span class="keyword">COUNT</span>(*) &gt; <span class="number">4</span> <span class="comment">--对学生总人数进行筛选</span></span><br></pre></td></tr></table></figure>

<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*从选课表“SC”中查询出各学生的平均分高于80的“学号”和“平均分”*/</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="string">&#x27;学号&#x27;</span> = <span class="keyword">SID</span>, <span class="string">&#x27;平均分&#x27;</span> = <span class="keyword">AVG</span>(Scores)</span><br><span class="line"><span class="keyword">FROM</span> SC</span><br><span class="line"><span class="keyword">GROUP</span> <span class="keyword">BY</span> <span class="keyword">SID</span></span><br><span class="line"><span class="keyword">HAVING</span> <span class="keyword">AVG</span>(Scores)  &gt; <span class="number">80</span></span><br></pre></td></tr></table></figure>
<h2 id="Order-by子句—排序查询"><a href="#Order-by子句—排序查询" class="headerlink" title="Order by子句—排序查询"></a>Order by子句—排序查询</h2><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">ORDER BY 列名表达式表 ASC / DESC</span><br></pre></td></tr></table></figure>
<p>按一列或多列对查询结果进行升序（ASC ：默认）或降序（DESC）排序。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*从学生表“ “Student”中查询出各专业学生的入学平均分,并将结果按降序排序*/</span></span><br><span class="line"><span class="keyword">SELECT</span> Specialty <span class="keyword">AS</span>  专业, <span class="keyword">AVG</span>(AScores) <span class="keyword">AS</span> <span class="string">&#x27;平均分&#x27;</span></span><br><span class="line"><span class="keyword">FROM</span> Student</span><br><span class="line"><span class="keyword">GROUP</span> <span class="keyword">BY</span> Specialty <span class="comment">-- 根据专业名称分组</span></span><br><span class="line"><span class="keyword">ORDER</span> <span class="keyword">BY</span> <span class="keyword">AVG</span>(AScores) <span class="keyword">DESC</span> <span class="comment">--按分数降序排序</span></span><br></pre></td></tr></table></figure>

<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*从学生表“Student”中查询出软件技术专业学生信息，并按照性别升序和入学成绩降序排列 */</span></span><br><span class="line"><span class="keyword">SELECT</span> *</span><br><span class="line"><span class="keyword">FROM</span> Student</span><br><span class="line"><span class="keyword">WHERE</span> Specialty=<span class="string">&#x27; 软件技术&#x27;</span></span><br><span class="line"><span class="keyword">ORDER</span> <span class="keyword">BY</span> Sex <span class="keyword">ASC</span>, AScores <span class="keyword">DESC</span>  </span><br><span class="line"><span class="comment">-- 按照性别升序和入学成绩降序排列</span></span><br></pre></td></tr></table></figure>

<h2 id="Into子句—保存查询"><a href="#Into子句—保存查询" class="headerlink" title="Into子句—保存查询"></a>Into子句—保存查询</h2><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">INTO 新表名</span><br></pre></td></tr></table></figure>

<ul>
<li>INTO子句指定使用结果集来 <code>创建新表</code> </li>
<li>查询结果往往需要保存下来以便使用，使用INTO子句可以将查询结果存储在一个新建的基本表中，这种方式常用于<code>创建表的副本</code></li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*从选课表“SC”中将学号和课程号的内容保存为新表student_course*/</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="keyword">SID</span>, CID</span><br><span class="line"><span class="keyword">INTO</span> student_course <span class="comment">-- 创建新表</span></span><br><span class="line"><span class="keyword">FROM</span> SC</span><br></pre></td></tr></table></figure>

<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*巧用INTO 语句将表student 复制成student_1*/</span></span><br><span class="line"><span class="keyword">SELECT</span> *</span><br><span class="line"><span class="keyword">INTO</span> student_1</span><br><span class="line"><span class="keyword">FROM</span> student</span><br></pre></td></tr></table></figure>
<h1 id="ANSI连接查询"><a href="#ANSI连接查询" class="headerlink" title="ANSI连接查询"></a>ANSI连接查询</h1><p>在SQL Server 中，<code>实现表的连接 </code>可使用 <code>两种语法形式</code>：</p>
<ul>
<li><p>一种 是前面介绍的FROM 子句，连接条件写在WHERE子句的逻辑表达式中，从而实现表的连接 （早期连接方式）</p>
</li>
<li><p>另一种是<code>ANSI </code>（American National StandardsInstitute— 美国国家标准学会）连接语法形式，在FROM子句中使用<code>JOIN…ON </code>关键字，连接条件写在<code>ON</code>之后，从而实现表的连接。</p>
</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*从教务管理数据库“EDUC”中查询出学生的学号、姓名、所选课程名和成绩信息*/</span></span><br><span class="line"><span class="comment">--早期连接方式： （方式一）</span></span><br><span class="line"><span class="keyword">SELECT</span> x.SID, x.Sname, z. Cname, y.Scores</span><br><span class="line"><span class="keyword">FROM</span> Student x, SC y, Course z</span><br><span class="line"><span class="keyword">WHERE</span> x.SID=y.SID <span class="keyword">AND</span> y.CID=z.CID  <span class="comment">-- 连接条件</span></span><br><span class="line"></span><br><span class="line"><span class="comment">--ANSI 连接方式：  （方式二）</span></span><br><span class="line"><span class="keyword">SELECT</span> x.SID, x.Sname, z. Cname, y.Scores</span><br><span class="line"><span class="keyword">FROM</span> Student x <span class="keyword">JOIN</span> SC y <span class="keyword">ON</span> x.SID=y.SID <span class="comment">-- 连接条件</span></span><br><span class="line">	<span class="keyword">JOIN</span> Course z <span class="keyword">ON</span> y.CID=z.CID</span><br></pre></td></tr></table></figure>

<figure class="highlight"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 语法</span></span><br><span class="line">FROM &#123; 表名1 [ 连接类型] JOIN  表名2 ON  连接条件&#125; [,…,n]</span><br></pre></td></tr></table></figure>

<ul>
<li>实现表与表的两两连接 ，表1和表2连接之后还可以继续与表3,…, 表n 连接，最多可以连接64 个表 。</li>
<li>连接条件放在ON 关键字后 </li>
</ul>
<p><img src="https://cdn.jsdelivr.net/gh/xiaoliblog/image@bfdd61046f86537e464e398b191222fd030d196a/2021/01/17/289da43469ac6e2097526bed5c7b9bfa.png"></p>
<h2 id="内连接"><a href="#内连接" class="headerlink" title="内连接"></a>内连接</h2><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">FROM 表名1 [INNER] JOIN 表名2 ON 连接表达式</span><br></pre></td></tr></table></figure>
<p>从表1 和表2 的<code>笛卡儿积 </code>中 ， 选出符合连接条件的数据行；如果数据行无法满足连接条件 ， 则将其丢弃 。 <code>内连接消除了与另一个表中不匹配的数据行</code></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*从教务管理数据库“EDUC”中查询每个学生的学号、姓名、课程名及选课成绩，不允许有重复列（3表内连）*/</span></span><br><span class="line"><span class="keyword">USE</span> EDUC</span><br><span class="line"><span class="keyword">GO</span></span><br><span class="line"><span class="keyword">SELECT</span> x.SID , x.Sname, z.Cname, y.Scores</span><br><span class="line"><span class="keyword">FROM</span> Student x <span class="keyword">INNER</span> <span class="keyword">JOIN</span> SC y <span class="keyword">ON</span> x.SID = y.SID</span><br><span class="line"><span class="keyword">INNER</span> <span class="keyword">JOIN</span> Course z <span class="keyword">ON</span> y.CID = z.CID</span><br></pre></td></tr></table></figure>

<h2 id="外连接"><a href="#外连接" class="headerlink" title="外连接"></a>外连接</h2><ul>
<li><p>外连接返回FROM 子句中指定的至少一个表( 或视图) 中的所有行 （不包含ON 之后的连接条件 ） ， 只要这些行符合任何WHERE 选择或HAVING 限定条件 。</p>
</li>
<li><p>外连接又分为<code>左外连接</code>、 <code>右外连接 </code>和<code>全外连接</code>。</p>
</li>
<li><p>左外连接 对连接中 <code>左边的表不加限制</code>； 右外连接 对连接中<code>右边的表不加限制</code> ； 全外连接 对<code>两个表都不加限制</code> ， 两个表中的所有行都会包括在结果集中 </p>
</li>
<li><p><strong>左外连接</strong></p>
</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">FROM  表名1 LEFT [OUTER] JOIN 表名2 ON  连接表达式</span><br></pre></td></tr></table></figure>
<p>连接结果 <code>保留表1</code>没形成连接的行  ，<code>表2 </code>相应的各列为NULL值 </p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*在教务管理数据库“EDUC”中，查询哪些课程没人选？*/</span></span><br><span class="line"><span class="keyword">SELECT</span> Course.* , SC.*</span><br><span class="line"><span class="keyword">FROM</span> Course <span class="keyword">LEFT</span> <span class="keyword">OUTER</span> <span class="keyword">JOIN</span> SC</span><br><span class="line"><span class="keyword">ON</span> Course.CID=SC.CID</span><br></pre></td></tr></table></figure>
<p><img src="https://cdn.jsdelivr.net/gh/xiaoliblog/image@acd66e5b00db1fd565cc2edea70d850173d45648/2021/01/17/98d761a92f2b8bde900043b9b539b539.png"></p>
<ul>
<li><strong>右外连接</strong></li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">FROM  表名1 RIGHT [OUTER] JOIN  表名2 ON  连接表达式</span><br></pre></td></tr></table></figure>
<p>连接 <code>结果保留表2 </code>没形成连接的行，<code>表1 </code>相应的列为NULL值</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*从表“Borrow ”和表“Book”中查询出图书被借阅的情况， 包括没有被借的图书情况*/</span></span><br><span class="line"><span class="keyword">SELECT</span> x.RID, x.BID, y.BID, y.Bname, y.Author</span><br><span class="line"><span class="keyword">FROM</span> Borrow x <span class="keyword">RIGHT</span> <span class="keyword">OUTER</span> <span class="keyword">JOIN</span> Book y <span class="comment">--- 右外连接</span></span><br><span class="line"><span class="keyword">ON</span> x.BID=y.BID</span><br></pre></td></tr></table></figure>
<p><img src="https://cdn.jsdelivr.net/gh/xiaoliblog/image@4c2cc82a1f3f71f9ea81419c5e31600939d6ea0e/2021/01/17/5a1ad7392920548f12456492db7b18ca.png"></p>
<ul>
<li><strong>全外连接</strong></li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">FROM  表名1 FULL [OUTER] JOIN  表名2 ON 连接表达式</span><br></pre></td></tr></table></figure>
<p>连接结果 保留表1 没形成连接的元组，表2 相应的列为NULL值；<br>连接结果也 保留表2 没形成连接的元组，表1 相应的列为NULL值。<br><img src="https://cdn.jsdelivr.net/gh/xiaoliblog/image@38975dfb75f63e9d5cd3c6312f97792d782e952c/2021/01/17/ed3f200190ed72a1a0950dc163050663.png"><br><img src="https://cdn.jsdelivr.net/gh/xiaoliblog/image@0216ad5746735606ec5e93eb0ae08a0e0a89c5c5/2021/01/17/b24c20c3acaf38dcd3746afa9b706eef.png"></p>
<h2 id="自连接"><a href="#自连接" class="headerlink" title="自连接"></a>自连接</h2><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">FROM  表名1  别名1 JOIN  表名1  别名2 ON 连接表达式</span><br></pre></td></tr></table></figure>

<ul>
<li>表可以通过自连接实现自身的连接运算。自连接可以看作是 “<code> 一张表的两个副本之间进行的连接</code>“</li>
<li>在自连接中，必须为表指定两个不同的别名，使之在逻辑上成为两张表。</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*从图书管理数据库“Library”中查询出借了两本以上图书的读者的借书信息*/</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 步骤1:表“ Borrow”与表“Borrow”自连接</span></span><br><span class="line"><span class="keyword">SELECT</span> x.RID, x.BID, x.ReturnDate, y.RID, y.BID</span><br><span class="line"><span class="keyword">FROM</span> Borrow x <span class="keyword">JOIN</span> Borrow y <span class="keyword">ON</span> x.RID=y.RI  <span class="comment">-- 读者编号等值条件自连接</span></span><br><span class="line"><span class="keyword">WHERE</span> x.ReturnDate <span class="keyword">IS</span> <span class="literal">NULL</span></span><br><span class="line"><span class="keyword">AND</span> y.ReturnDate <span class="keyword">IS</span> <span class="literal">NULL</span> <span class="comment">-- 还期为空，即尚未归还</span></span><br><span class="line"></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 步骤2:对表“Borrow”与表“Borrow”自连接加选择条件和投影操作</span></span><br><span class="line"><span class="keyword">SELECT</span> x.RID, x.BID, x.ReturnDate <span class="comment">--投影去重复列名 </span></span><br><span class="line"><span class="keyword">FROM</span> Borrow x <span class="keyword">JOIN</span> Borrow y</span><br><span class="line"><span class="keyword">ON</span> x.RID=y.RID <span class="comment">--读者编号等值条件自连接 </span></span><br><span class="line"><span class="keyword">WHERE</span> x.ReturnDate <span class="keyword">IS</span> <span class="literal">NULL</span></span><br><span class="line"><span class="keyword">AND</span> y.ReturnDate <span class="keyword">IS</span> <span class="literal">NULL</span> <span class="comment">--还期为空，即尚未归还 </span></span><br><span class="line"><span class="keyword">AND</span> x.BID&lt;&gt;y.BID <span class="comment">--图书编号不同，即两本不同的书</span></span><br></pre></td></tr></table></figure>

<h2 id="交叉连接"><a href="#交叉连接" class="headerlink" title="交叉连接"></a>交叉连接</h2><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">FROM 表名1 CROSS JOIN 表名2</span><br><span class="line"><span class="comment">-- 或：</span></span><br><span class="line">FROM 表名1 ,表名2 <span class="comment">---兼容早期连接的写法</span></span><br></pre></td></tr></table></figure>
<p>两个表进行 <code>笛卡尔积</code> 计算 ， 结果集的行数等于表1的行数 乘以表2的行数 </p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*从教务管理数据库“EDUC”中查询每个学生的学号、姓名、课程代号及选课成绩*/</span></span><br><span class="line"><span class="keyword">SELECT</span> x.SID, x.Sname , y.CID, y.Scores</span><br><span class="line"><span class="keyword">FROM</span> Student x,SC y</span><br><span class="line"><span class="keyword">WHERE</span> x.SID = y.SID</span><br></pre></td></tr></table></figure>

<h2 id="多表连接"><a href="#多表连接" class="headerlink" title="多表连接"></a>多表连接</h2><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">FROM 表名1 JOIN 表名2 ON 连接表达式</span><br><span class="line">JOIN 表名3 ON 连接表达式[,… ，n]</span><br></pre></td></tr></table></figure>
<p>最多可以连接64个表，通常为8 ～10个 </p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/* 对读者表“Reader ”、借阅表“Borrow”和图书表 “Book”三个表进行等值连接,查询读者“程鹏”的借阅情况*/</span></span><br><span class="line"><span class="keyword">SELECT</span> t1.RID, t1.Rname, t3.Bname, t2.LendDate</span><br><span class="line"><span class="keyword">FROM</span> Reader t1 <span class="keyword">JOIN</span> Borrow t2 <span class="keyword">ON</span> t1.RID=t2.RID</span><br><span class="line">			<span class="keyword">JOIN</span> Book t3 <span class="keyword">ON</span> t2.BID=t3.BID</span><br><span class="line"><span class="keyword">WHERE</span> t1.Rname=<span class="string">&#x27;程鹏&#x27;</span></span><br></pre></td></tr></table></figure>
<h1 id="子查询"><a href="#子查询" class="headerlink" title="子查询"></a>子查询</h1><ul>
<li>在实际应用中，经常要用到 多层嵌套查询。</li>
<li>若一条select语句的where子句中又包含了另一个select语句，此时就出现了查询的嵌套（外层select语句嵌套了内层select语句）。</li>
<li>外层的select语句被称为<code>父查询或主查询</code>；内层的select语句称为<code>子查询</code>。</li>
</ul>
<ul>
<li><strong>单值嵌套查询</strong><br>在该方式下，通过 子查询返回一个单一的数值，该数据可以参加相关表达式的运算</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/* 从选课表“SC”中查询出学号最小的学生的选课信息*/</span></span><br><span class="line"><span class="keyword">USE</span> EDUC</span><br><span class="line"><span class="keyword">GO</span></span><br><span class="line"><span class="keyword">SELECT</span> *</span><br><span class="line"><span class="keyword">FROM</span> SC</span><br><span class="line"><span class="keyword">WHERE</span> <span class="keyword">SID</span> = ( <span class="keyword">SELECT</span> <span class="keyword">MIN</span>( <span class="keyword">SID</span> ) <span class="keyword">FROM</span> Student )</span><br></pre></td></tr></table></figure>

<ul>
<li><strong>[NOT] IN 子查询</strong></li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">列名 [NOT] IN (子查询)</span><br></pre></td></tr></table></figure>
<p>列值<code>被包含</code>或 <code>不被包含</code>在集合中</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*查询出选修了课程号16020010的学生的学号、姓名 */</span></span><br><span class="line"><span class="keyword">USE</span> EDUC</span><br><span class="line"><span class="keyword">GO</span></span><br><span class="line"><span class="keyword">SELECT</span> Student.SID, Student.Sname</span><br><span class="line"><span class="keyword">FROM</span> Student   <span class="comment">--- 对于表Student 中的每一个记录</span></span><br><span class="line"><span class="keyword">WHERE</span> <span class="keyword">SID</span> <span class="keyword">IN</span>   <span class="comment">--- 判断其SID 是否在子查询的集合当中</span></span><br><span class="line">( <span class="keyword">SELECT</span> <span class="keyword">SID</span></span><br><span class="line"><span class="keyword">FROM</span> SC</span><br><span class="line"><span class="keyword">WHERE</span> CID = <span class="string">&#x27;16020010&#x27;</span> ) <span class="comment">--- 课号为16020010 的SID</span></span><br></pre></td></tr></table></figure>

<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*查询出没有选修任何课程的学生的学号、姓名*/</span></span><br><span class="line"><span class="keyword">USE</span> EDUC</span><br><span class="line"><span class="keyword">GO</span></span><br><span class="line"><span class="keyword">SELECT</span> Student.SID, Student.Sname</span><br><span class="line"><span class="keyword">FROM</span> Student</span><br><span class="line"><span class="keyword">WHERE</span> <span class="keyword">SID</span> <span class="keyword">NOT</span> <span class="keyword">IN</span> ( <span class="keyword">SELECT</span> <span class="keyword">SID</span> <span class="keyword">FROM</span> SC )</span><br></pre></td></tr></table></figure>

<ul>
<li><strong>[NOT] EXISTS 子查询</strong></li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">[NOT] EXISTS (子查询)</span><br></pre></td></tr></table></figure>
<p>当子查询的结果存在( 不为空集) 时，返回逻辑“真”值；否则返回逻辑“假”值。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*查询出选修了课程号16020010的学生的学号、姓名 */</span></span><br><span class="line"><span class="keyword">USE</span> EDUC</span><br><span class="line"><span class="keyword">GO</span></span><br><span class="line"><span class="keyword">SELECT</span> Student.SID, Student.Sname</span><br><span class="line"><span class="keyword">FROM</span> Student <span class="comment">--- 对于Student 表里的每一个SID</span></span><br><span class="line"><span class="keyword">WHERE</span> <span class="keyword">EXISTS</span> <span class="comment">--- 是否满足子查询的选择条件</span></span><br><span class="line">( <span class="keyword">SELECT</span> *</span><br><span class="line"><span class="keyword">FROM</span> SC</span><br><span class="line"><span class="keyword">WHERE</span> Student.SID = SC.SID <span class="keyword">AND</span> CID = <span class="string">&#x27;16020010&#x27;</span> )</span><br></pre></td></tr></table></figure>

<ul>
<li><strong>比较子查询</strong></li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">列名  比较运算符 ALL | ANY | SOME(子查询)</span><br></pre></td></tr></table></figure>

<ul>
<li><p>当列名的值在关系上满足子查询中的值时，逻辑表达式的值为真，否则为假。</p>
</li>
<li><p>ANY 和SOME 的用法相同</p>
</li>
<li><p><strong>ALL比较子查询</strong></p>
</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">列名 比较运算符 ALL ( 子查询)</span><br></pre></td></tr></table></figure>
<p>当列名的值在关系上满足子查询中的 所有值时，逻辑表达式的值为真，否则为假</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*从借阅表“Borrow ”中查询出读者编号RID最大的读者的借书情况。*/</span></span><br><span class="line"><span class="keyword">SELECT</span> *</span><br><span class="line"><span class="keyword">FROM</span> Borrow</span><br><span class="line"><span class="keyword">WHERE</span> RID &gt;= <span class="keyword">ALL</span>( <span class="keyword">SELECT</span> RID <span class="keyword">FROM</span> Reader) <span class="comment">--子查询</span></span><br></pre></td></tr></table></figure>

<ul>
<li><strong>ANY |SOME 比较子查询</strong></li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">列名 比较运算符 ANY | SOME (子查询)</span><br></pre></td></tr></table></figure>

<ul>
<li>当列名的值在关系上满足子查询中的 任何一个值 时 ， 逻辑表达式的值为真 ， 否则为假 。</li>
<li>当比较运算符为“ = ” 时 ， “<code>列名=ANY(子查询)</code> ” 和 “ <code>列名IN ( 子查询) </code>” 所描述的条件是同样的 。</li>
<li>ANY 和SOME的用法相同 的用法相同 </li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*从图书数据库“Library”中查询当前借出的清华大学出版社的图书借期等信息*/</span></span><br><span class="line"><span class="keyword">SELECT</span> x.RID, x.BID, x.LendDate,y.Bname, y.Publisher, y.LentOut</span><br><span class="line"><span class="keyword">FROM</span> Borrow x, Book y  <span class="comment">-- 对于借阅表中的每借出的一本书</span></span><br><span class="line"><span class="keyword">WHERE</span> x.BID = <span class="keyword">ANY</span> <span class="comment">-- 判断其图书编号是否在子查询集合中</span></span><br><span class="line">(<span class="keyword">SELECT</span> BID <span class="comment">-- 已经借出清华大学出版社的图书编号集合</span></span><br><span class="line"><span class="keyword">FROM</span> Book</span><br><span class="line"><span class="keyword">WHERE</span> LentOut= <span class="string">&#x27;True&#x27;</span> <span class="keyword">AND</span> Publisher=<span class="string">&#x27;清华大学出版社&#x27;</span>)  </span><br><span class="line"><span class="comment">-- 已经借出的清华大学出版社出版的图书</span></span><br><span class="line"><span class="keyword">AND</span> x.BID=y.BID <span class="comment">--等值连接</span></span><br></pre></td></tr></table></figure>
<h1 id="联合查询"><a href="#联合查询" class="headerlink" title="联合查询"></a>联合查询</h1><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">SELECT_1  UNION [ALL]</span><br><span class="line">SELECT_2  UNION [ALL]</span><br><span class="line">……</span><br><span class="line">SELECT_n</span><br></pre></td></tr></table></figure>
<p>对n 次查询结果进行并运算。ALL 表示<code>运算结果包括重复行</code></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*查询出“人民邮电出版社”和“清华大学出版社”出版的图书的作者名。 包括重复行。 （联合同一个表、同一字段！）*/</span></span><br><span class="line"><span class="keyword">USE</span> <span class="keyword">Library</span></span><br><span class="line"><span class="keyword">GO</span></span><br><span class="line"><span class="keyword">SELECT</span> Author</span><br><span class="line"><span class="keyword">FROM</span> Book</span><br><span class="line"><span class="keyword">WHERE</span> publisher = <span class="string">&#x27; 人民邮电出版社&#x27;</span></span><br><span class="line"><span class="keyword">UNION</span> <span class="keyword">ALL</span> <span class="comment">---  并运算，包括重复行</span></span><br><span class="line"><span class="keyword">SELECT</span> Author</span><br><span class="line"><span class="keyword">FROM</span> Book</span><br><span class="line"><span class="keyword">WHERE</span> publisher = <span class="string">&#x27; 清华大学出版社&#x27;</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 结果等同于下列语句</span></span><br><span class="line"><span class="keyword">USE</span> <span class="keyword">Library</span></span><br><span class="line"><span class="keyword">GO</span></span><br><span class="line"><span class="keyword">SELECT</span> Author</span><br><span class="line"><span class="keyword">FROM</span> Books</span><br><span class="line"><span class="keyword">WHERE</span> publisher = <span class="string">&#x27; 人民邮电出版社&#x27;</span> <span class="keyword">OR</span> publisher = <span class="string">&#x27; 清华大学出版社&#x27;</span></span><br></pre></td></tr></table></figure></article><div class="post-copyright"><div class="post-copyright__author"><span class="post-copyright-meta">文章作者: </span><span class="post-copyright-info"><a href="mailto:undefined">小李博客</a></span></div><div class="post-copyright__type"><span class="post-copyright-meta">文章链接: </span><span class="post-copyright-info"><a href="https://xiaoliblog.cn">https://xiaoliblog.cn</a></span></div><div class="post-copyright__notice"><span class="post-copyright-meta">版权声明: </span><span class="post-copyright-info">本博客所有文章除特别声明外，均采用 <a href="https://creativecommons.org/licenses/by-nc-sa/4.0/" target="_blank">CC BY-NC-SA 4.0</a> 许可协议。转载请注明来自 <a href="http://xiaoliblog.cn" target="_blank">小李博客</a>！</span></div></div><div class="tag_share"><div class="post-meta__tag-list"><a class="post-meta__tags" href="/tags/SQLServer/">SQLServer</a></div><div class="post_share"><div class="social-share" data-image="https://cdn.jsdelivr.net/gh/lzyblog/image@main/2021/01/01/09bc0b6b22736e2ddda50dbb14f3e553.png" data-sites="facebook,twitter,wechat,weibo,qq"></div><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/social-share.js/dist/css/share.min.css" media="print" onload="this.media='all'"><script src="https://cdn.jsdelivr.net/npm/social-share.js/dist/js/social-share.min.js" defer></script></div></div><div class="post-reward"><div class="reward-button button--animated"><i class="fas fa-qrcode"></i> 打赏</div><div class="reward-main"><ul class="reward-all"><li class="reward-item"><a href="/img/wechat.png" target="_blank"><img class="post-qr-code-img" src="/img/wechat.png" alt="微信"/></a><div class="post-qr-code-desc">微信</div></li><li class="reward-item"><a href="/img/alipay.png" target="_blank"><img class="post-qr-code-img" src="/img/alipay.png" alt="支付宝"/></a><div class="post-qr-code-desc">支付宝</div></li></ul></div></div><nav class="pagination-post" id="pagination"><div class="prev-post pull-left"><a href="/page/JavaWeb17.html"><img class="prev-cover" src="https://cdn.jsdelivr.net/gh/xiaoliblog/image@41021477288a29257324f7413b81c299b3dec54e/2021/03/21/3d05e9b134d98fc70cebf887c1359f71.png" onerror="onerror=null;src='https://cdn.jsdelivr.net/gh/lzyblog/image@main/2020/11/19/bd16b394f7359083b1f6072a67e3f968.png'" alt="cover of previous post"><div class="pagination-info"><div class="label">上一篇</div><div class="prev_info">JavaWeb🌍smbms登录流程实现</div></div></a></div><div class="next-post pull-right"><a href="/page/JavaWeb16.html"><img class="next-cover" src="https://cdn.jsdelivr.net/gh/xiaoliblog/image@41021477288a29257324f7413b81c299b3dec54e/2021/03/21/3d05e9b134d98fc70cebf887c1359f71.png" onerror="onerror=null;src='https://cdn.jsdelivr.net/gh/lzyblog/image@main/2020/11/19/bd16b394f7359083b1f6072a67e3f968.png'" alt="cover of next post"><div class="pagination-info"><div class="label">下一篇</div><div class="next_info">JavaWeb🌍smbms项目搭建</div></div></a></div></nav><div class="relatedPosts"><div class="headline"><i class="fas fa-thumbs-up fa-fw"></i><span> 相关推荐</span></div><div class="relatedPosts-list"><div><a href="/page/sqlserver01.html" title="SQLServer📦T-SQL编程基础"><img class="cover" src="https://cdn.jsdelivr.net/gh/lzyblog/image@main/2021/01/01/09bc0b6b22736e2ddda50dbb14f3e553.png" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2021-01-18</div><div class="title">SQLServer📦T-SQL编程基础</div></div></a></div><div><a href="/page/sqlserver04.html" title="SQLServer📦存储过程"><img class="cover" src="https://cdn.jsdelivr.net/gh/lzyblog/image@main/2021/01/01/09bc0b6b22736e2ddda50dbb14f3e553.png" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2021-01-18</div><div class="title">SQLServer📦存储过程</div></div></a></div><div><a href="/page/sqlserver07.html" title="SQLServer📦数据库创建与管理"><img class="cover" src="https://cdn.jsdelivr.net/gh/lzyblog/image@main/2021/01/01/09bc0b6b22736e2ddda50dbb14f3e553.png" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2021-01-15</div><div class="title">SQLServer📦数据库创建与管理</div></div></a></div><div><a href="/page/sqlserver02.html" title="SQLServer📦表的创建与管理"><img class="cover" src="https://cdn.jsdelivr.net/gh/lzyblog/image@main/2021/01/01/09bc0b6b22736e2ddda50dbb14f3e553.png" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2021-01-15</div><div class="title">SQLServer📦表的创建与管理</div></div></a></div><div><a href="/page/sqlserver08.html" title="SQLServer📦索引"><img class="cover" src="https://cdn.jsdelivr.net/gh/lzyblog/image@main/2021/01/01/09bc0b6b22736e2ddda50dbb14f3e553.png" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2021-01-18</div><div class="title">SQLServer📦索引</div></div></a></div><div><a href="/page/sqlserver05.html" title="SQLServer📦视图"><img class="cover" src="https://cdn.jsdelivr.net/gh/lzyblog/image@main/2021/01/01/09bc0b6b22736e2ddda50dbb14f3e553.png" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2021-01-17</div><div class="title">SQLServer📦视图</div></div></a></div></div></div><hr/><div id="post-comment"><div class="comment-head"><div class="comment-headline"><i class="fas fa-comments fa-fw"></i><span> 评论</span></div></div><div class="comment-wrap"><div><div id="twikoo-wrap"></div></div></div></div></div><div class="aside-content" id="aside-content"><div class="card-widget card-info"><div class="card-info-avatar is-center"><img class="avatar-img" src="https://cdn.jsdelivr.net/gh/xiaoliblog/image@6b5e7ef72be1c8973d94e5a9c49accbf775ad820/2021/02/01/c485da031fe0e464d04eaba8a66c4a8f.png" onerror="this.onerror=null;this.src='/img/friend_404.gif'" alt="avatar"/><div class="author-info__name">小李博客</div><div class="author-info__description">越努力，越幸运！</div></div><div class="card-info-data"><div class="card-info-data-item is-center"><a href="/archives/"><div class="headline">文章</div><div class="length-num">210</div></a></div><div class="card-info-data-item is-center"><a href="/tags/"><div class="headline">标签</div><div class="length-num">38</div></a></div><div class="card-info-data-item is-center"><a href="/categories/"><div class="headline">分类</div><div class="length-num">56</div></a></div></div><a class="button--animated" id="card-info-btn" target="_blank" rel="noopener" href="https://github.com/xiaoliblog"><i class="fab fa-github"></i><span>博主的GitHub首页</span></a><div class="card-info-social-icons is-center"><a class="social-icon" href="https://gitee.com/xiaoliblog" target="_blank" title="Gitee"><i class="iconfont icon-gitee card_icon_gitee"></i></a><a class="social-icon" href="https://space.bilibili.com/390969485" target="_blank" title="BiliBili"><i class="iconfont icon-bilibili card_icon_bilibili"></i></a><a class="social-icon" href="http://wpa.qq.com/msgrd?v=3&amp;uin=2312057536&amp;site=CSDN&amp;menu=yes" target="_blank" title="QQ"><i class="iconfont icon-qq card_icon_qq"></i></a><a class="social-icon" href="https://github.com/xiaoliblog" target="_blank" title="GitHub"><i class="iconfont icon-git card_icon_git"></i></a><a class="social-icon" href="https://blog.csdn.net/qq_43266250?spm=1010.2135.3001.5113" target="_blank" title="CSDN"><i class="iconfont icon-csdn card_icon_csdn"></i></a></div></div><div class="card-widget card-announcement"><div class="item-headline"><i class="fas fa-bullhorn card-announcement-animation"></i><span>公告</span></div><div class="announcement_content">正在考研备考中💦</div></div><div class="sticky_layout"><div class="card-widget" id="card-toc"><div class="item-headline"><i class="fas fa-stream"></i><span>目录</span></div><div class="toc-content"><ol class="toc"><li class="toc-item toc-level-1"><a class="toc-link" href="#Select%E6%9F%A5%E8%AF%A2%E8%AF%AD%E5%8F%A5"><span class="toc-number">1.</span> <span class="toc-text">Select查询语句</span></a><ol class="toc-child"><li class="toc-item toc-level-2"><a class="toc-link" href="#Select%E5%AD%90%E5%8F%A5%E2%80%94%E6%8A%95%E5%BD%B1%E6%9F%A5%E8%AF%A2"><span class="toc-number">1.1.</span> <span class="toc-text">Select子句—投影查询</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#From%E5%AD%90%E5%8F%A5%E2%80%94%E8%BF%9E%E6%8E%A5%E6%9F%A5%E8%AF%A2"><span class="toc-number">1.2.</span> <span class="toc-text">From子句—连接查询</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#Where%E5%AD%90%E5%8F%A5%E2%80%94%E9%80%89%E6%8B%A9%E6%9F%A5%E8%AF%A2"><span class="toc-number">1.3.</span> <span class="toc-text">Where子句—选择查询</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#Group-by%E5%AD%90%E5%8F%A5%E2%80%94%E5%88%86%E7%BB%84%E7%BB%9F%E8%AE%A1%E6%9F%A5%E8%AF%A2"><span class="toc-number">1.4.</span> <span class="toc-text">Group by子句—分组统计查询</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#Having%E5%AD%90%E5%8F%A5%E2%80%94%E9%99%90%E5%AE%9A%E6%9F%A5%E8%AF%A2"><span class="toc-number">1.5.</span> <span class="toc-text">Having子句—限定查询</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#Order-by%E5%AD%90%E5%8F%A5%E2%80%94%E6%8E%92%E5%BA%8F%E6%9F%A5%E8%AF%A2"><span class="toc-number">1.6.</span> <span class="toc-text">Order by子句—排序查询</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#Into%E5%AD%90%E5%8F%A5%E2%80%94%E4%BF%9D%E5%AD%98%E6%9F%A5%E8%AF%A2"><span class="toc-number">1.7.</span> <span class="toc-text">Into子句—保存查询</span></a></li></ol></li><li class="toc-item toc-level-1"><a class="toc-link" href="#ANSI%E8%BF%9E%E6%8E%A5%E6%9F%A5%E8%AF%A2"><span class="toc-number">2.</span> <span class="toc-text">ANSI连接查询</span></a><ol class="toc-child"><li class="toc-item toc-level-2"><a class="toc-link" href="#%E5%86%85%E8%BF%9E%E6%8E%A5"><span class="toc-number">2.1.</span> <span class="toc-text">内连接</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E5%A4%96%E8%BF%9E%E6%8E%A5"><span class="toc-number">2.2.</span> <span class="toc-text">外连接</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E8%87%AA%E8%BF%9E%E6%8E%A5"><span class="toc-number">2.3.</span> <span class="toc-text">自连接</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E4%BA%A4%E5%8F%89%E8%BF%9E%E6%8E%A5"><span class="toc-number">2.4.</span> <span class="toc-text">交叉连接</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E5%A4%9A%E8%A1%A8%E8%BF%9E%E6%8E%A5"><span class="toc-number">2.5.</span> <span class="toc-text">多表连接</span></a></li></ol></li><li class="toc-item toc-level-1"><a class="toc-link" href="#%E5%AD%90%E6%9F%A5%E8%AF%A2"><span class="toc-number">3.</span> <span class="toc-text">子查询</span></a></li><li class="toc-item toc-level-1"><a class="toc-link" href="#%E8%81%94%E5%90%88%E6%9F%A5%E8%AF%A2"><span class="toc-number">4.</span> <span class="toc-text">联合查询</span></a></li></ol></div></div><div class="card-widget card-recent-post"><div class="item-headline"><i class="fas fa-history"></i><span>最新文章</span></div><div class="aside-list"><div class="aside-list-item"><a class="thumbnail" href="/page/project01.html" title="前端实例🥳响应式网站首页"><img src="https://cdn.jsdelivr.net/gh/xiaoliblog/image@c19917500ab083c77c7613263ba7ee74d5a08ae6/2021/04/30/469f30b141d73fa0fc4c962662d5813f.png" onerror="this.onerror=null;this.src='https://cdn.jsdelivr.net/gh/lzyblog/image@main/2020/11/19/bd16b394f7359083b1f6072a67e3f968.png'" alt="前端实例🥳响应式网站首页"/></a><div class="content"><a class="title" href="/page/project01.html" title="前端实例🥳响应式网站首页">前端实例🥳响应式网站首页</a><time datetime="2021-04-30T11:50:53.094Z" title="发表于 2021-04-30 19:50:53">2021-04-30</time></div></div><div class="aside-list-item"><a class="thumbnail" href="/page/WeChatpay.html" title="微信支付对接"><img src="https://cdn.jsdelivr.net/gh/xiaoliblog/image@f2f670b92ea149650ffa7834354fc90284f2f44a/2021/04/29/5bdc9b381a06193d27cf2fb7c2fb608a.png" onerror="this.onerror=null;this.src='https://cdn.jsdelivr.net/gh/lzyblog/image@main/2020/11/19/bd16b394f7359083b1f6072a67e3f968.png'" alt="微信支付对接"/></a><div class="content"><a class="title" href="/page/WeChatpay.html" title="微信支付对接">微信支付对接</a><time datetime="2021-04-29T12:20:48.070Z" title="发表于 2021-04-29 20:20:48">2021-04-29</time></div></div><div class="aside-list-item"><a class="thumbnail" href="/page/Alipay.html" title="支付宝支付对接"><img src="https://cdn.jsdelivr.net/gh/xiaoliblog/image@61a9b6a6e09e4bda38bb08e3104b717885beaee5/2021/04/29/c3fa51f9cf14e90d9e5a7aa8814dd041.png" onerror="this.onerror=null;this.src='https://cdn.jsdelivr.net/gh/lzyblog/image@main/2020/11/19/bd16b394f7359083b1f6072a67e3f968.png'" alt="支付宝支付对接"/></a><div class="content"><a class="title" href="/page/Alipay.html" title="支付宝支付对接">支付宝支付对接</a><time datetime="2021-04-27T16:00:00.000Z" title="发表于 2021-04-28 00:00:00">2021-04-28</time></div></div><div class="aside-list-item"><a class="thumbnail" href="/page/Vuejs11.html" title="Vue.js前端框架🎯Pagination+PageHelper实现分页"><img src="https://cdn.jsdelivr.net/gh/xiaoliblog/image@61bf4245f6d84d2d0f66d163b89c916788cc1845/2021/04/13/ec6b232f8fe5a840e4bd8c3eabcf49b2.png" onerror="this.onerror=null;this.src='https://cdn.jsdelivr.net/gh/lzyblog/image@main/2020/11/19/bd16b394f7359083b1f6072a67e3f968.png'" alt="Vue.js前端框架🎯Pagination+PageHelper实现分页"/></a><div class="content"><a class="title" href="/page/Vuejs11.html" title="Vue.js前端框架🎯Pagination+PageHelper实现分页">Vue.js前端框架🎯Pagination+PageHelper实现分页</a><time datetime="2021-04-26T14:48:39.701Z" title="发表于 2021-04-26 22:48:39">2021-04-26</time></div></div><div class="aside-list-item"><a class="thumbnail" href="/page/Vuejs10.html" title="Vue.js前端框架🎯Vue-Element-admin模版"><img src="https://cdn.jsdelivr.net/gh/xiaoliblog/image@61bf4245f6d84d2d0f66d163b89c916788cc1845/2021/04/13/ec6b232f8fe5a840e4bd8c3eabcf49b2.png" onerror="this.onerror=null;this.src='https://cdn.jsdelivr.net/gh/lzyblog/image@main/2020/11/19/bd16b394f7359083b1f6072a67e3f968.png'" alt="Vue.js前端框架🎯Vue-Element-admin模版"/></a><div class="content"><a class="title" href="/page/Vuejs10.html" title="Vue.js前端框架🎯Vue-Element-admin模版">Vue.js前端框架🎯Vue-Element-admin模版</a><time datetime="2021-04-19T16:00:00.000Z" title="发表于 2021-04-20 00:00:00">2021-04-20</time></div></div></div></div></div></div></main><footer id="footer"><div id="footer-wrap"><div class="copyright">&copy;2020 - 2021 By 小李博客</div><div class="framework-info"><span>框架 </span><a target="_blank" rel="noopener" href="https://hexo.io">🎯Hexo</a><span class="footer-separator">|</span><span>主题 </span><a target="_blank" rel="noopener" href="https://github.com/jerryc127/hexo-theme-butterfly">🎉Butterfly</a></div><div class="footer_custom_text"><span><a style="margin-inline:5px" target="_blank" href="https://hexo.io/"><img src="https://img.shields.io/badge/Frame-Hexo-blue?style=flat&logo=hexo" title="博客框架为Hexo"></a><a style="margin-inline:5px" target="_blank" href="https://butterfly.js.org/"><img src="https://img.shields.io/badge/Theme-Butterfly-6513df?style=flat&logo=bitdefender" title="主题采用butterfly"></a><a style="margin-inline:5px" target="_blank" href="https://www.jsdelivr.com/"><img src="https://img.shields.io/badge/CDN-jsDelivr-orange?style=flat&logo=jsDelivr" title="本站使用JsDelivr为静态资源提供CDN加速"></a><a style="margin-inline:5px" target="_blank" href="https://vercel.com/ "><img src="https://img.shields.io/badge/Hosted-Vercel-brightgreen?style=flat&logo=Vercel" title="本站采用双线部署，默认线路托管于Vercel"></a><a style="margin-inline:5px" target="_blank" href="https://coding.net/ "><img src="https://img.shields.io/badge/Hosted-Coding-0cedbe?style=flat&logo=Codio" title="本站采用双线部署，联通线路托管于Coding"></a><a style="margin-inline:5px" target="_blank" href="https://github.com/"><img src="https://img.shields.io/badge/Source-Github-d021d6?style=flat&logo=GitHub" title="本站项目由Gtihub托管"></a><a style="margin-inline:5px" target="_blank" href="http://creativecommons.org/licenses/by-nc-sa/4.0/"><img src="https://img.shields.io/badge/Copyright-BY--NC--SA%204.0-d42328?style=flat&logo=Claris" title="本站采用知识共享署名-非商业性使用-相同方式共享4.0国际许可协议进行许可"></a></span></div><div class="icp"><a target="_blank" rel="noopener" href="https://beian.miit.gov.cn/"><img class="icp-icon" src="/img/icp.png" alt="ICP"/><span>湘ICP备2021002541号</span></a></div></div></footer></div><div id="rightside"><div id="rightside-config-hide"><button id="readmode" type="button" title="阅读模式"><i class="fas fa-book-open"></i></button><button id="font-plus" type="button" title="放大字体"><i class="fas fa-plus"></i></button><button id="font-minus" type="button" title="缩小字体"><i class="fas fa-minus"></i></button><button id="translateLink" type="button" title="简繁转换">繁</button><button id="darkmode" type="button" title="浅色和深色模式转换"><i class="fas fa-adjust"></i></button><button id="hide-aside-btn" type="button" title="单栏和双栏切换"><i class="fas fa-arrows-alt-h"></i></button></div><div id="rightside-config-show"><button id="rightside_config" type="button" title="设置"><i class="fas fa-cog fa-spin"></i></button><button class="close" id="mobile-toc-button" type="button" title="目录"><i class="fas fa-list-ul"></i></button><button id="chat_btn" type="button" title="rightside.chat_btn"><i class="fas fa-sms"></i></button><a id="to_comment" href="#post-comment" title="直达评论"><i class="fas fa-comments"></i></a><button id="go-up" type="button" title="回到顶部"><i class="fas fa-arrow-up"></i></button></div></div><div id="algolia-search"><div class="search-dialog"><div class="search-dialog__title" id="algolia-search-title">Algolia</div><div id="algolia-input-panel"><div id="algolia-search-input"></div></div><hr/><div id="algolia-search-results"><div id="algolia-hits"></div><div id="algolia-pagination"></div><div id="algolia-stats"></div></div><span class="search-close-button"><i class="fas fa-times"></i></span></div><div id="search-mask"></div></div><div><script src="/js/utils.js"></script><script src="https://cdn.jsdelivr.net/npm/vue@2.6.11"></script><script src="/js/main.js"></script><script defer src="/js/tw_cn.js"></script><script defer src="https://cdn.jsdelivr.net/npm/medium-zoom/dist/medium-zoom.min.js"></script><script src="https://cdn.jsdelivr.net/npm/instant.page/instantpage.min.js" type="module" defer></script><script defer src="https://cdn.jsdelivr.net/npm/node-snackbar/dist/snackbar.min.js"></script><script defer src="/js/search/algolia.js"></script><div class="js-pjax"><script>if (!window.MathJax) {
  window.MathJax = {
    loader: {
      source: {
        '[tex]/amsCd': '[tex]/amscd'
      }
    },
    tex: {
      inlineMath: [ ['$','$'], ["\\(","\\)"]],
      tags: 'ams'
    },
    options: {
      renderActions: {
        findScript: [10, doc => {
          for (const node of document.querySelectorAll('script[type^="math/tex"]')) {
            const display = !!node.type.match(/; *mode=display/)
            const math = new doc.options.MathItem(node.textContent, doc.inputJax[0], display)
            const text = document.createTextNode('')
            node.parentNode.replaceChild(text, node)
            math.start = {node: text, delim: '', n: 0}
            math.end = {node: text, delim: '', n: 0}
            doc.math.push(math)
          }
        }, ''],
        addClass: [200,() => {
          document.querySelectorAll('mjx-container:not([display=\'true\']').forEach( node => {
            const target = node.parentNode
            if (!target.classList.contains('has-jax')) {
              target.classList.add('mathjax-overflow')
            }
          })
        }, '', false]
      }
    }
  }
  
  const script = document.createElement('script')
  script.src = 'https://cdn.jsdelivr.net/npm/mathjax@3/es5/tex-mml-chtml.js'
  script.id = 'MathJax-script'
  script.async = true
  document.head.appendChild(script)
} else {
  MathJax.startup.document.state(0)
  MathJax.texReset()
  MathJax.typeset()
}</script><script>if (document.getElementsByClassName('mermaid').length) {
  if (window.mermaidJsLoad) mermaid.init()
  else {
    getScript('https://cdn.jsdelivr.net/npm/mermaid/dist/mermaid.min.js').then(() => {
      window.mermaidJsLoad = true
      mermaid.initialize({
        theme: 'default',
      })
      false && mermaid.init()
    })
  }
}</script><script>(()=>{
  const $countDom = document.getElementById('twikoo-count')
  const init = () => {
    twikoo.init(Object.assign({
      el: '#twikoo-wrap',
      envId: 'xiaoliblog-8gj3j5045d5b0896',
      region: ''
    }, null))
  }

  const getCount = () => {
    twikoo.getCommentsCount({
      envId: 'xiaoliblog-8gj3j5045d5b0896',
      region: '',
      urls: [window.location.pathname],
      includeReply: false
    }).then(function (res) {
      $countDom.innerText = res[0].count
    }).catch(function (err) {
      console.error(err);
    });
  }

  const loadTwikoo = (bool = false) => {
    if (typeof twikoo === 'object') {
      init()
      bool && $countDom && setTimeout(getCount,0)
    } else {
      getScript('https://cdn.jsdelivr.net/npm/twikoo@1.3.0/dist/twikoo.all.min.js').then(()=> {
        init()
        bool && $countDom && setTimeout(getCount,0)
      })
    }
  }

  if ('Twikoo' === 'Twikoo' || !true) {
    if (true) btf.loadComment(document.getElementById('twikoo-wrap'), loadTwikoo)
    else loadTwikoo(true)
  } else {
    window.loadOtherComment = () => {
      loadTwikoo()
    }
  }
})()</script></div><script defer src="//lib.baomitu.com/jquery/3.5.1/jquery.min.js"></script><script defer src="https://myhkw.cn/api/player/160561664166" id="myhk" key="160561664166" m="1"></script><div><canvas id="snow" style="position:fixed;top:0;left:0;width:100%;height:100%;z-index:99999;pointer-events:none"></canvas></div><script>const notMobile = (!(navigator.userAgent.match(/(phone|pad|pod|iPhone|iPod|ios|iPad|Android|Mobile|BlackBerry|IEMobile|MQQBrowser|JUC|Fennec|wOSBrowser|BrowserNG|WebOS|Symbian|Windows Phone)/i)));</script><scrip async type="text/javascript" src="https://cdn.jsdelivr.net/gh/Candinya/Kratos-Rebirth@latest/source/js/snow.min.js"></scrip><scrip defer src="https://cdn.jsdelivr.net/npm/hexo-theme-volantis@latest/source/js/issues.min.js"></scrip><script async data-pjax src="//busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script><script>var gitcalendar = new Vue({
  el: '#gitcalendar',
  data: {
    simplemode: true, 
    user: 'xiaoliblog',
    fixed: 'fixed',
    px: 'px',
    x: '',
    y: '',
    span1: '',
    span2: '',
    month: ['一月', '二月', '三月', '四月', '五月', '六月', '七月', '八月', '九月', '十月', '十一月', '十二月'],
    monthchange: [],
    oneyearbeforeday: '',
    thisday: '',
    amonthago: '',
    aweekago: '',
    weekdatacore: 0,
    datacore: 0,
    total: 0,
    datadate: '',
    data: [],
    positionplusdata: [],
    firstweek: [],
    lastweek: [],
    beforeweek: [],
    thisweekdatacore: 0,
    mounthbeforeday: 0,
    mounthfirstindex: 0,
    crispedges: 'crispedges',
    thisdayindex: 0,
    amonthagoindex: 0,
    amonthagoweek: [],
    firstdate: [],
    first2date: [],
    montharrbefore: [],
    monthindex: 0,
    color: ['#ebedf0', '#f1f8ff', '#dbedff', '#c8e1ff', '#79b8ff', '#2188ff', '#0366d6', '#005cc5', '#044289', '#032f62', '#05264c']
  },
  methods: {
    selectStyle(data, event) {
      document.querySelector('.angle-wrapper').style.display = 'block'
      this.span1 = data.date;
      this.span2 = data.count;
      this.x = event.clientX - 100;
      this.y = event.clientY - 60
    },
    outStyle() {
      document.querySelector('.angle-wrapper').style.display = 'none'
    },
    thiscolor(x) {
      if (x === 0) {
        let i = parseInt(x / 2);
        return this.color[0]
      } else if (x < 2) {
        return this.color[1]
      } else if (x < 20) {
        let i = parseInt(x / 2);
        return this.color[i]
      } else {
        return this.color[9]
      }
    },
  }
});
var apiurl = 'python-github-calendar-api-ruby.vercel.app' ? 'https://python-github-calendar-api-ruby.vercel.app/api?' : 'https://githubapi.ryanchristian.dev/user/'
var githubapiurl = apiurl + gitcalendar.user;
//canvas绘图
function responsiveChart() {
  let c = document.getElementById("gitcanvas");
  if (c) {
    let cmessage = document.getElementById("gitmessage");
    let ctx = c.getContext("2d");
    c.width = document.getElementById("gitcalendarcanvasbox").offsetWidth;
    let linemaxwitdh = 0.96 * c.width / gitcalendar.data.length;
    c.height = 9 * linemaxwitdh;
    let lineminwitdh = 0.8 * linemaxwitdh;
    let setposition = {
      x: 0.02 * c.width,
      y: 0.025 * c.width
    };
    for (let week in gitcalendar.data) {
      weekdata = gitcalendar.data[week];
      for (let day in weekdata) {
        let dataitem = {
          date: "",
          count: "",
          x: 0,
          y: 0
        };
        gitcalendar.positionplusdata.push(dataitem);
        ctx.fillStyle = gitcalendar.thiscolor(weekdata[day].count);
        setposition.y = Math.round(setposition.y * 100) / 100;
        dataitem.date = weekdata[day].date;
        dataitem.count = weekdata[day].count;
        dataitem.x = setposition.x;
        dataitem.y = setposition.y;
        ctx.fillRect(setposition.x, setposition.y, lineminwitdh, lineminwitdh);
        setposition.y = setposition.y + linemaxwitdh
      };
      setposition.y = 0.025 * c.width;
      setposition.x = setposition.x + linemaxwitdh
    };
    ctx.font = "600  Arial";
    ctx.fillStyle = '#aaa';
    ctx.fillText("日", 0, 1.9 * linemaxwitdh);
    ctx.fillText("二", 0, 3.9 * linemaxwitdh);
    ctx.fillText("四", 0, 5.9 * linemaxwitdh);
    ctx.fillText("六", 0, 7.9 * linemaxwitdh);
    let monthindexlist = c.width / 24;
    for (let index in gitcalendar.monthchange) {
      ctx.fillText(gitcalendar.monthchange[index], monthindexlist, 0.7 * linemaxwitdh);
      monthindexlist = monthindexlist + c.width / 12
    };
    cmessage.onmousemove = function(event) {
      document.querySelector('.angle-wrapper').style.display = 'none'
    };
    c.onmousemove = function(event) {
      document.querySelector('.angle-wrapper').style.display = 'none'
      getMousePos(c, event);
    };

    function getMousePos(canvas, event) {
      var rect = canvas.getBoundingClientRect();
      var x = event.clientX - rect.left * (canvas.width / rect.width);
      var y = event.clientY - rect.top * (canvas.height / rect.height);
      //console.log("x:"+x+",y:"+y);
      for (let item of gitcalendar.positionplusdata) {
        let lenthx = x - item.x;
        let lenthy = y - item.y;
        //console.log(lenthx,lenthy);
        if (0 < lenthx && lenthx < lineminwitdh) {
          if (0 < lenthy && lenthy < lineminwitdh) {
            //console.log(item.date,item.count)
            document.querySelector('.angle-wrapper').style.display = 'block'
            gitcalendar.span1 = item.date;
            gitcalendar.span2 = item.count;
            gitcalendar.x = event.clientX - 100;
            gitcalendar.y = event.clientY - 60
          }
        }
        //if(0< x - item.x <lineminwitdh&&0< y - item.y <lineminwitdh){
        //console.log(item.count,item.date);
        //}
      }
    }
  }
}
//数据统计算法
function addlastmonth() {
  if (gitcalendar.thisdayindex === 0) {
    thisweekcore(52);
    thisweekcore(51);
    thisweekcore(50);
    thisweekcore(49);
    thisweekcore(48);
    gitcalendar.thisweekdatacore += gitcalendar.firstdate[6].count;
    gitcalendar.amonthago = gitcalendar.firstdate[6].date
  } else {
    thisweekcore(52);
    thisweekcore(51);
    thisweekcore(50);
    thisweekcore(49);
    thisweek2core();
    gitcalendar.amonthago = gitcalendar.first2date[gitcalendar.thisdayindex - 1].date
  }
};

function thisweek2core() {
  for (let i = gitcalendar.thisdayindex - 1; i < gitcalendar.first2date.length; i++) {
    gitcalendar.thisweekdatacore += gitcalendar.first2date[i].count
  }
};

function thisweekcore(index) {
  for (let item of gitcalendar.data[index]) {
    gitcalendar.thisweekdatacore += item.count
  }
};

function addlastweek() {
  for (let item of gitcalendar.lastweek) {
    gitcalendar.weekdatacore += item.count
  }
};

function addbeforeweek() {
  for (let i = gitcalendar.thisdayindex; i < gitcalendar.beforeweek.length; i++) {
    gitcalendar.weekdatacore += gitcalendar.beforeweek[i].count
  }
};

function addweek(data) {
  if (gitcalendar.thisdayindex === 6) {
    gitcalendar.aweekago = gitcalendar.lastweek[0].date;
    addlastweek()
  } else {
    lastweek = data.contributions[51];
    gitcalendar.aweekago = lastweek[gitcalendar.thisdayindex + 1].date;
    addlastweek();
    addbeforeweek()
  }
}

fetch(githubapiurl)
  .then(data => data.json())
  .then(data => {
    gitcalendar.data = data.contributions;
    gitcalendar.total = data.total;
    gitcalendar.first2date = gitcalendar.data[48];
    gitcalendar.firstdate = gitcalendar.data[47];
    gitcalendar.firstweek = data.contributions[0];
    gitcalendar.lastweek = data.contributions[52];
    gitcalendar.beforeweek = data.contributions[51];
    gitcalendar.thisdayindex = gitcalendar.lastweek.length - 1;
    gitcalendar.thisday = gitcalendar.lastweek[gitcalendar.thisdayindex].date;
    gitcalendar.oneyearbeforeday = gitcalendar.firstweek[0].date;
    gitcalendar.monthindex = gitcalendar.thisday.substring(5, 7) * 1;
    gitcalendar.montharrbefore = gitcalendar.month.splice(gitcalendar.monthindex, 12 - gitcalendar.monthindex);
    gitcalendar.monthchange = gitcalendar.montharrbefore.concat(gitcalendar.month);
    addweek(data);
    addlastmonth();
    responsiveChart();
  })
  .catch(function(error) {
    console.log(error);
  });

//手机版更换为svg绘制
if (document.getElementById("gitcalendarcanvasbox").offsetWidth < 500) {
  gitcalendar.simplemode = false
}

//当改变窗口大小时重新绘制canvas
window.onresize = function() {
  if (gitcalendar.simplemode) responsiveChart()
}

//解决滚动滑轮时出现的标签显示
window.onscroll = function() {
  if (document.querySelector('.angle-wrapper')) {
    document.querySelector('.angle-wrapper').style.display = 'none'
  }
};</script></div><script src="/live2dw/lib/L2Dwidget.min.js?094cbace49a39548bed64abff5988b05"></script><script>L2Dwidget.init({"pluginRootPath":"live2dw/","pluginJsPath":"lib/","pluginModelPath":"assets/","tagMode":false,"log":false,"model":{"jsonPath":"/live2dw/assets/hijiki.model.json"},"display":{"position":"right","width":150,"height":300},"mobile":{"show":true},"react":{"opacity":0.7}});</script></body></html>